package tech.xs.dao.Impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Repository;

import tech.xs.dao.UserDao;
import tech.xs.entity.User;
import tech.xs.util.BaseDao;

/** 
* @author 作者 Your-Name:席盛 
* @version 创建时间：2020年2月13日 下午4:36:31 
* @类说明
*/
@Repository
public class UserDaoImpl extends BaseDao implements UserDao {

	@Override
	public int add(User user)  {
		String sql="insert into smbms_user(userCode,userName,userPassword,gender,birthday,phone,address,userRole,createdBy,creationDate,idPicPath,workPicPath) values(?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params= {user.getUserCode(),user.getUserName(),user.getUserPassword(),user.getGender(),user.getBirthday(),user.getPhone(),user.getAddress(),user.getUserRole(),user.getCreatedBy(),user.getCreationDate(),user.getIdPicPath(),user.getWorkPicPath()};
		dopstmt(sql,params);
		int row=0;
		try {
			row=getUpdateCount();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return row;
	}

	@Override
	public User getLoginUser(String userCode,String userPassword)  {
		String sql="select * from smbms_user where userCode=? and userPassword=?";
		Object[] params= {userCode,userPassword};
		dopstmt(sql, params);
		ResultSet rs=null;
		User user=null;
		try {
			rs=getResultSet();
			if(rs.next()) {
				user=new User();
				user.setId(rs.getInt("id"));
				user.setUserCode(rs.getString("userCode"));
				user.setUserName(rs.getString("userName"));
				user.setUserPassword(rs.getString("userPassword"));
				user.setGender(rs.getInt("gender"));
				user.setBirthday(rs.getDate("birthday"));
				user.setPhone(rs.getString("phone"));
				user.setAddress(rs.getString("address"));
				user.setUserRole(rs.getInt("userRole"));
				user.setCreatedBy(rs.getInt("createdBy"));
				user.setCreationDate(rs.getTimestamp("creationDate"));
				user.setModifyBy(rs.getInt("modifyBy"));
				user.setModifyDate(rs.getTimestamp("modifyDate"));
			}
			return user;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeAll(rs);
		}
		return null;
	}

	@Override
	public List<User> getUserList(String userName, int userRole, int currentPageNo, int pageSize){
		// TODO Auto-generated method stub
		List<User> list=new ArrayList<User>();
		String sql = "select * from smbms_user where 1=1";
		if (userName!=null && userName.length()!=0) {
			sql+=" and userName like '%"+userName+"%'";
		}
		if (userRole!=0) {
			sql+=" and userRole="+userRole+"";
		}
		sql+=" ORDER BY id DESC limit ?,?";
		System.out.println(sql);
		Object [] params = {currentPageNo,pageSize};
		dopstmt(sql, params);
		ResultSet rs=null;
		try {
			rs=getResultSet();
			while (rs.next()) {
				list.add(new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getInt(5),rs.getDate(6),rs.getString(7),rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getDate(11),rs.getInt(12),rs.getDate(13),rs.getString(14),rs.getString(15)));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeAll(rs);
		}
		System.out.println(list.size()+"list长度");
		return list;
	}
	@Override
	public int getUserCount(String userName, int userRole)  {
		int usercount=0;
		String sql="select count(1) from smbms_user where 1=1";
		if (userName!=null && userName.length()!=0) {
			sql+=" and userName like '%"+userName+"%'";
		}
		if (userRole!=0) {
			sql+=" and userRole="+userRole+"";
		}
		dopstmt(sql);
		ResultSet rs=null;
		try {
			rs=getResultSet();
			if (rs.next()) {
				usercount=rs.getInt(1);
			}
			return usercount;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeAll(rs);
		}
		return 0;
	}

	@Override
	public int deleteUserById(Integer delId)  {
		String sql="delete from smbms_user where id=?";
		Object[] params= {delId};
		dopstmt(sql, params);
		int row=0;
		try {
			row=getUpdateCount();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return row;
	}

	@Override
	public User getUserById(Integer id)  {
		String sql="SELECT * FROM smbms_user where id=?";
		Object[] params= {id};
		dopstmt(sql, params);
		ResultSet rs=null;
		User user=null;
		try {
			rs=getResultSet();
			while (rs.next()) {
				user =new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getInt(5),rs.getDate(6),rs.getString(7),rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getDate(11),rs.getInt(12),rs.getDate(13),rs.getString(14),rs.getString(15));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeAll(rs);
		}
		return user;
	}

	@Override
	public int modify(User user)  {
		String sql="update smbms_user set userName=?,gender=?,birthday=?,phone=?,address=?,userRole=? where id=? ";
		Object[] params= {user.getUserName(),user.getGender(),user.getBirthday(),user.getPhone(),user.getAddress(),user.getUserRole(),user.getId()};
		dopstmt(sql, params);
		int row=0;
		try {
			row=getUpdateCount();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return row;
	}

	@Override
	public int updatePwd(int id, String pwd)  {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public User getUserCode(String userCode) {
		String sql="SELECT * FROM smbms_user where userCode=?";
		Object[] params= {userCode};
		dopstmt(sql, params);
		ResultSet rs=null;
		User user=null;
		try {
			rs=getResultSet();
			if (rs.next()) {
				user= new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getInt(5),rs.getDate(6),rs.getString(7),rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getDate(11),rs.getInt(12),rs.getDate(13),rs.getString(14),rs.getString(15));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			closeAll(rs);
		}
		System.out.println(user);
		return user;
	}

}
